package com.tengju.bff.interfaces.covercharge.util;

import com.tengju.bff.interfaces.shared.ColumnVO;
import com.tengju.bff.interfaces.shared.KVVO;
import com.tengju.bff.interfaces.shared.TableVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author Sunny 数据导出到Excel工具
 */
public class ExcelExportUtil {

    // 定制日期格式
    private static String DATE_FORMAT = "yyyy-m-d";
    // 定制浮点数格式
    private static String NUMBER_FORMAT = " #,##0.00 ";
    private String xlsFileName;
    private HSSFWorkbook workbook;
    private List<HSSFSheet> sheets;
    private HSSFRow row;

    /**
     * 初始化Excel
     *
     * @param fileName 导出文件名
     */
    public ExcelExportUtil(String fileName) {
        this.xlsFileName = fileName;
        this.workbook = new HSSFWorkbook();
        sheets = new ArrayList<>(1);
        this.sheets.add(workbook.createSheet());
    }

    /**
     * 初始化Excel
     */
    public ExcelExportUtil() {
        this.workbook = new HSSFWorkbook();
        sheets = new ArrayList<>(1);
        this.sheets.add(workbook.createSheet());
    }

    public ExcelExportUtil(int sheetIndex, String... sheetNames) {
        this.workbook = new HSSFWorkbook();
        sheets = new ArrayList<>(sheetIndex);
        for (int i = 0; i < sheetIndex; i++) {
            HSSFSheet sheet = workbook.createSheet(sheetNames[i]);
            sheet.setDefaultColumnWidth(16);
            this.sheets.add(sheet);
        }
    }


    /**
     * 导出Excel文件
     */
    public void exportXLS() {
        try {
            FileOutputStream fOut = new FileOutputStream(xlsFileName);
            workbook.write(fOut);
            fOut.flush();
            fOut.close();
        } catch (Exception e) {
            e.printStackTrace();

        }

    }

    /**
     * 下载导出文件
     *
     * @param response
     * @param filename
     * @throws IOException
     */
    public void downloadExcel(HttpServletRequest request, HttpServletResponse response, String filename) {
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            response.setHeader("Content-disposition", "attachment;filename=" + getFileName(request, filename));
            response.setContentType("application/msexcel;charset=UTF-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            workbook.write(out);
        } catch (Exception e) {
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {

                }

            }

        }

    }

    public static String getFileName(HttpServletRequest request, String fileName) {
        String userAgent = request.getHeader("User-Agent").toLowerCase();
        String finalFileName = null;
        //chrome头也包含safari,需要排除chrome
        if(userAgent.contains("safari") && !userAgent.contains("chrome")){
            //处理safari的乱码问题
            byte[] bytesName = fileName.getBytes(Charset.forName("UTF-8"));
            finalFileName = new String(bytesName, Charset.forName("ISO-8859-1"));
        }else {
            try {
                finalFileName = URLEncoder.encode(fileName, "UTF-8");
            } catch (Throwable e) {
            }
        }
        return finalFileName;
    }

    /**
     * 增加一行
     *
     * @param index 行号
     */
    public void createRow(int index) {
        this.row = this.sheets.get(0).createRow(index);
    }

    /**
     * 设置字符串单元格
     *
     * @param index 列号
     * @param value 单元格填充值
     */
    @SuppressWarnings("deprecation")
    public void setCell(int index, String value) {
        HSSFCell cell = this.row.createCell((short) index);
        if (value == null) {
            cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        } else {
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString(value));
        }

    }

    /**
     * 设置日期类型单元格
     *
     * @param index 列号
     * @param value 单元格填充值
     */
    @SuppressWarnings("deprecation")
    public void setCell(int index, Date value) {
        HSSFCell cell = this.row.createCell((short) index);
        if (value == null) {
            cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        } else {
            cell.setCellValue(value);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFDataFormat format = workbook.createDataFormat();
            cellStyle.setDataFormat(format.getFormat(DATE_FORMAT));
            cell.setCellStyle(cellStyle);
        }

    }

    /**
     * 设置Int类型单元格
     *
     * @param index 列号
     * @param value 单元格填充值
     */
    @SuppressWarnings("deprecation")
    public void setCell(int index, Integer value) {
        HSSFCell cell = this.row.createCell((short) index);
        if (value == null) {
            cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        } else {
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(value);
        }

    }

    /**
     * 设置Double类型单元格
     *
     * @param index 列号
     * @param value 单元格填充值
     */
    @SuppressWarnings("deprecation")
    public void setCell(int index, Double value) {
        HSSFCell cell = this.row.createCell((short) index);
        if (value == null) {
            cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        } else {
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(value);
            HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
            HSSFDataFormat format = workbook.createDataFormat();
            cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
            cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
        }
    }

    /**
     * 设置Long类型单元格
     *
     * @param index 列号
     * @param value 单元格填充值
     */
    @SuppressWarnings("deprecation")
    public void setCell(int index, Long value) {
        HSSFCell cell = this.row.createCell((short) index);
        if (value == null) {
            cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        } else {
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString(String.valueOf(value)));
        }
    }

    /**
     * @return the xlsFileName
     */
    public String getXlsFileName() {
        return xlsFileName;
    }

    /**
     * @param xlsFileName the xlsFileName to set
     */
    public void setXlsFileName(String xlsFileName) {
        this.xlsFileName = xlsFileName;
    }

    /**
     * @return the workbook
     */
    public HSSFWorkbook getWorkbook() {
        return workbook;
    }

    /**
     * @param workbook the workbook to set
     */
    public void setWorkbook(HSSFWorkbook workbook) {
        this.workbook = workbook;
    }

    public void downloadExcelWithZip(HttpServletRequest request, HttpServletResponse response, String filename) {
        // 临时目录插入
        filename = filename + ".xls";
        String tempPath = System.getProperty("java.io.tmpdir") + filename;

        FileOutputStream output = null;
        try {
            output = new FileOutputStream(tempPath);
        } catch (FileNotFoundException e) {
            if (output != null) {
                try {
                    output.flush();
                    output.close();
                } catch (IOException ex) {

                }

            }
        }
        try {
            workbook.write(output);//写入磁盘
        } catch (Exception e) {
        } finally {
            if (output != null) {
                try {
                    output.flush();
                    output.close();
                } catch (IOException e) {

                }

            }

        }

        OutputStream out = null;
        try {
            out = response.getOutputStream();
            response.setHeader("Content-disposition", "attachment;filename=" + getFileName(request, filename));
            response.setContentType("application/msexcel;charset=UTF-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            workbook.write(out);
        } catch (Exception e) {
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {

                }
            }
        }
    }

    public void create(int sheetIndex, int x, int y, List<KVVO> kvvos) {
        this.row = this.sheets.get(sheetIndex).createRow(y);
        HSSFCellStyle titleCellStyle = getTitleCellStyle();
        HSSFCellStyle valueCellStyle = getValueCellStyle();
        for (int i = x; i < kvvos.size() + x; i++) {
            HSSFCell cell = this.row.createCell((short) i);
            if (kvvos.get(i - x) == null) {
                cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                cell.setCellStyle(titleCellStyle);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(new HSSFRichTextString(String.valueOf(kvvos.get(i - x).getLabel())));
                cell.setCellStyle(titleCellStyle);
            }
        }
        this.row = this.sheets.get(sheetIndex).createRow(y + 1);
        for (int i = x; i < kvvos.size() + x; i++) {
            HSSFCell cell = this.row.createCell((short) i);
            if (kvvos.get(i - x) == null) {
                cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                cell.setCellStyle(valueCellStyle);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(new HSSFRichTextString(String.valueOf(kvvos.get(i - x).getValue())));
                cell.setCellStyle(valueCellStyle);
            }
        }
    }

    public void create(int sheetIndex, int x, int y, TableVO tableVO) {
        this.row = this.sheets.get(sheetIndex).createRow(y);
        HSSFCellStyle titleCellStyle = getTitleCellStyle();
        for (int i = x; i < tableVO.getHeader().size() + x; i++) {
            HSSFCell cell = this.row.createCell((short) i);
            cell.setCellStyle(titleCellStyle);
            if (tableVO.getHeader().get(i - x) == null) {
                cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(new HSSFRichTextString(String.valueOf(tableVO.getHeader().get(i - x).getLabel())));
            }
        }
        createRecords(sheetIndex, x, y, tableVO);
    }

    public void createRecords(int sheetIndex, int x, int y, TableVO tableVO) {
        HSSFCellStyle valueCellStyle = getValueCellStyle();
        if (!CollectionUtils.isEmpty(tableVO.getRecords())) {
            for (int i = x; i < tableVO.getRecords().size() + x; i++) {
                y++;
                this.row = this.sheets.get(sheetIndex).createRow(y);
                Map<String, Object> map = tableVO.getRecords().get(i - x);
                int cellIndex = x;
                for (ColumnVO columnVO : tableVO.allChildHeader()) {
                    HSSFCell cell = this.row.createCell((short) cellIndex);
                    cell.setCellStyle(valueCellStyle);
                    cellIndex++;
                    if (map.get(columnVO.getProp()) == null) {
                        cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                    } else {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(new HSSFRichTextString(String.valueOf(map.get(columnVO.getProp()))));
                    }
                }
            }
        }
    }

    public void create(int sheetIndex, TableVO tableVO) {
        create(sheetIndex, 0, 0, tableVO);
    }

    private HSSFCellStyle getValueCellStyle() {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 居中
        setAlignment(cellStyle);
        // 设置边框
        setBorder(cellStyle);

        // 设置字体
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);

        return cellStyle;
    }

    private void setAlignment(HSSFCellStyle cellStyle) {
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    }

    private void setBorder(HSSFCellStyle cellStyle) {
        //下边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //左边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //上边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //右边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    }

    public HSSFCellStyle getTitleCellStyle() {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 居中
        setAlignment(cellStyle);
        // 设置背景色
        cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 设置边框
        //下边框
        setBorder(cellStyle);

        // 设置字体
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        //粗体显示
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyle.setFont(font);

        return cellStyle;
    }

    /**
     * 填充单元格并合并
     *
     * @param sheetIndex
     * @param x
     * @param y
     * @param tableVO
     */
    public void fillAndMergeHeader(int sheetIndex, int x, int y, TableVO tableVO) {
        int headerDepth = tableVO.headerDepth();
        int headerWidth = tableVO.headerWidth();
        HSSFCellStyle titleCellStyle = getTitleCellStyle();
        // 初始化x,y矩阵内所有单元格
        for (int depth = y; depth < headerDepth + y; depth++) {
            this.row = this.sheets.get(sheetIndex).createRow(depth);
            for (int width = x; width < headerWidth + x; width++) {
                HSSFCell cell = this.row.createCell((short) width);
                cell.setCellStyle(titleCellStyle);
                cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
            }
        }

        List<ColumnVO> allHeader = tableVO.allHeader();
        for (ColumnVO columnVO : allHeader) {
            Integer rowFrom = columnVO.getRowFrom();
            int rowTo = columnVO.getRowTo() - 1;
            Integer colFrom = columnVO.getColFrom();
            int colTo = columnVO.getColTo() - 1;

            // from to相等时，不用merge
            if(rowFrom != rowTo || colFrom != colTo){
                this.sheets.get(sheetIndex).addMergedRegion(new CellRangeAddress(
                        rowFrom, rowTo, colFrom, colTo
                ));
            }

            HSSFRow row = this.sheets.get(sheetIndex).getRow(rowFrom);
            HSSFCell rowCell = row.getCell(colFrom);
            rowCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            rowCell.setCellValue(new HSSFRichTextString(String.valueOf(columnVO.getLabel())));
        }
    }
}
